const { getCaseDatabaseInfo } = require('../database/knex');

/**
 * SearchService - Handles global search operations using SQLite FTS5
 */
class SearchService {

    /**
     * Execute global full-text search
     * @param {string} caseId - Case ID
     * @param {string} query - Search query text
     * @param {object} options - Options (limit, offset)
     */
    static async search(caseId, query, options = {}) {
        const { limit = 50, offset = 0 } = options;

        if (!query || query.trim().length === 0) {
            return { results: [], total: 0 };
        }

        try {
            const { knex } = await getCaseDatabaseInfo(caseId);

            // Validate table exists first (in case FTS failed to init)
            const exists = await knex.raw("SELECT name FROM sqlite_master WHERE type='table' AND name='fts_data_index'");
            if (exists.length === 0) {
                throw new Error('Search index not available');
            }

            // Prepare FTS query
            // Escape double quotes in query to prevent syntax errors
            const safeQuery = query.replace(/"/g, '""');
            // Use simple prefix matching for now
            const ftsQuery = `"${safeQuery}"*`;

            const results = await knex('fts_data_index')
                .select('*')
                .whereRaw('fts_data_index MATCH ?', [ftsQuery])
                .orderByRaw('rank')
                .limit(limit)
                .offset(offset);

            // Get total (approximate or separate query)
            // Note: COUNT(*) on FTS match can be slow, but acceptable for small datasets
            const [{ count }] = await knex('fts_data_index')
                .count('* as count')
                .whereRaw('fts_data_index MATCH ?', [ftsQuery]);

            return {
                results: results,
                total: count,
                query: query
            };

        } catch (error) {
            console.error(`[SearchService] Error searching case ${caseId}:`, error);
            // Return empty results gracefully
            return { results: [], total: 0, error: error.message };
        }
    }

    /**
     * Index data record
     * @param {object} trx - Transaction or Knex instance
     * @param {string} tableName - Source table name
     * @param {object} data - Data row
     * @param {string} dataType - Data type
     */
    static async indexRecord(trx, tableName, data, dataType) {
        // Construct searchable content
        let contentArray = [];
        let title = '';

        // Strategy based on data type
        switch (dataType) {
            case 'bank':
                contentArray = [
                    data.account_number,
                    data.account_name,
                    data.counterparty_name,
                    data.counterparty_account,
                    data.remarks,
                    data.summary,
                    data.transaction_amount
                ];
                title = `${data.transaction_time ? data.transaction_time.substring(0, 10) : ''} 银行转账 ${data.transaction_amount}`;
                break;
            case 'call':
                contentArray = [
                    data.phone_number,
                    data.other_number,
                    data.user_name,
                    data.location
                ];
                title = `${data.start_time ? data.start_time.substring(0, 10) : ''} 通话记录 ${data.other_number}`;
                break;
            case 'personal_info':
                contentArray = [
                    data.name,
                    data.id_card,
                    data.phone,
                    data.address,
                    data.workplace
                ];
                title = `人员信息: ${data.name}`;
                break;
            default:
                // Generic fallback: join all string values
                contentArray = Object.values(data).filter(v => typeof v === 'string');
                title = `${dataType} 记录`;
        }

        const content = contentArray.filter(v => v !== null && v !== undefined).join(' ');

        if (!content) return;

        await trx('fts_data_index').insert({
            original_table: tableName,
            original_id: data.id || 0, // Assuming ID is returned or known. If inserting batch, we might not know ID immediately if using basic insert.
            data_type: dataType,
            title: title || '未命名数据',
            content: content
        });
    }

    /**
     * Index Batch for better performance
     */
    static async indexBatch(trx, tableName, batch, dataType) {
        const ftsRows = batch.map(row => {
            let contentArray = [];
            let title = '';

            switch (dataType) {
                case 'bank':
                    contentArray = [row.account_number, row.account_name, row.counterparty_name, row.counterparty_account, row.remarks, row.summary, row.transaction_amount];
                    title = `银行转账 ${row.transaction_amount}`;
                    break;
                case 'call':
                    contentArray = [row.phone_number, row.other_number, row.user_name, row.location];
                    title = `通话 ${row.other_number}`;
                    break;
                case 'personal_info':
                    contentArray = [row.name, row.id_card, row.phone, row.address, row.workplace];
                    title = `人员 ${row.name}`;
                    break;
                default:
                    contentArray = Object.values(row).filter(v => typeof v === 'string');
                    title = `${dataType}`;
            }

            const content = contentArray.filter(v => v).join(' ');

            // Note: We need the ID of the inserted row to link back. 
            // Often batch insert returns IDs or we assume sequence.
            // If we don't have IDs (e.g. SQLite limit), we might just store 0 or skip linking for now?
            // SQLite insert returns lastInsertRowid, but not for batch.
            // WORKAROUND: For now, we unfortunately can't cheaply link back to exact ID `original_id` in a pure batch insert + FTS batch insert without querying back.
            // We'll set original_id to 0 for batch inputs or handle strictly row-by-row if linking is critical.
            // Since this is "Global Search", finding the text is priority. Clicking it might just take you to the table view with a filter.

            return {
                original_table: tableName,
                original_id: row.id || 0, // ID might be missing in batch
                data_type: dataType,
                title: title,
                content: content
            };
        });

        if (ftsRows.length > 0) {
            await trx('fts_data_index').insert(ftsRows);
        }
    }
}

module.exports = SearchService;
